SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TCMOVI_CAJA_Q01]
/*-----------------------------------------------------------*/
/*-- Empresa		:  OFISIS S.A.			   --*/
/*-- Cliente    	:  OFISIS S.A.			   --*/
/*-- Sistema      	:  POSICION CAJA		   --*/
/*-- Modulo      	:  EMPRESA			   --*/
/*-- Programa   	:  REPORTE DE POSICION CAJA	  --*/
/*-- Script		:  SP_TCMOVI_CAJA_Q01.SQL	   --*/
/*-- Nombre SP		:  SP_TCMOVI_CAJA_Q01 		   --*/
/*-- Desarrollado por 	:  D.CH				   --*/
/*-- Fecha		:  21/02/05 			   --*/
/*-- Base de Datos	:  MICROSOFT SQL SERVER 8.0	   --*/
/*-----------------------------------------------------------*/

/*-----------------------------------------------------------*/
/*-- MODIFICADO POR 	:  D,CH.			--*/
/*-- Fecha		:  21/02/05 			   --*/
/*-- DETALLE	  	:  A SQL			   --*/
/*--							   --*/
/*-----------------------------------------------------------*/

/*--------------------------------------------------------------*/
/*--  DROP PROC SP_TCMOVI_CAJA_Q01				*/
/*--  GRANT ALL ON SP_TCMOVI_CAJA_Q01 TO PUBLIC 			*/
/*--  SP_TCMOVI_CAJA_Q01 'OFISIS', '01', '01', 'SOL', 'SOL', '05/29/2000'  */
/*--------------------------------------------------------------*/
@ISCO_USUA TD_VC_008,
@IDFE_REPO TD_DT_001

AS

-- INGRESOS
EXECUTE ('
INSERT INTO #TWPOSI_CAJA_Q01_01( CO_EMPR, CO_CAJA, IM_INGR, IM_EGRE, IM_CHEQ_CART )
Select T1.CO_EMPR, T1.CO_CAJA, SUM( ISNULL(T1.IM_MOVI,0) ), 0, 0
From   TCMOVI_CAJA T1
Where T1.TI_MOVI = "I"
AND T1.TI_SITU = "ACT"
AND T1.FE_OPER = "' + @IDFE_REPO + '" 
AND Exists ( Select CO_EMPR From  TTUSER_CAJA t4
	Where        T1.CO_EMPR = t4.CO_EMPR
	And            T1.CO_CAJA  = t4.CO_CAJA
	And            t4.CO_USUA = "' + @ISCO_USUA + '"
	And            t4.ST_REPO = "S") 
GROUP BY T1.CO_EMPR, T1.CO_CAJA')

-- EGRESOS
EXECUTE('
INSERT INTO #TWPOSI_CAJA_Q01_01( CO_EMPR, CO_CAJA, IM_INGR, IM_EGRE, IM_CHEQ_CART )
Select T1.CO_EMPR, T1.CO_CAJA, 0, SUM( ISNULL(T1.IM_MOVI,0) ), 0
From   TCMOVI_CAJA T1
Where  T1.TI_MOVI = "E"
AND T1.TI_SITU = "ACT"
AND T1.FE_OPER = "' + @IDFE_REPO + '" 
AND Exists ( Select CO_EMPR From  TTUSER_CAJA t4
	Where        T1.CO_EMPR = t4.CO_EMPR
	And            T1.CO_CAJA  = t4.CO_CAJA
	And            t4.CO_USUA = "' + @ISCO_USUA + '"
	And            t4.ST_REPO = "S") 
GROUP BY T1.CO_EMPR, T1.CO_CAJA')


INSERT INTO #TWPOSI_CAJA_Q01_02( CO_EMPR, CO_CAJA, IM_INGR, IM_EGRE, IM_CHEQ_CART )
Select CO_EMPR, CO_CAJA, SUM(IM_INGR), SUM(IM_EGRE), SUM(IM_CHEQ_CART)
FROM #TWPOSI_CAJA_Q01_01
GROUP BY CO_EMPR, CO_CAJA

-- INSERTO CUENTAS QUE NO HAN TENIDO MOVIMIENTOS, ES DECIR AUN NO ESTAN GRABADAS EN TABLA #TWSABA REPO

INSERT INTO #TWPOSI_CAJA_Q01_02( CO_EMPR, CO_CAJA, IM_INGR, IM_EGRE, IM_CHEQ_CART )
SELECT T2.CO_EMPR, T2.CO_CAJA, 0, 0, 0
FROM TMCAJA T2
WHERE NOT EXISTS
(SELECT T1.CO_EMPR FROM #TWPOSI_CAJA_Q01_02 T1
WHERE RTRIM(T1.CO_EMPR) = RTRIM(T2.CO_EMPR)
AND RTRIM(T1.CO_CAJA) = RTRIM(T2.CO_CAJA) )


/********************************* FIN ***********************************/


GO
